package com.tuanzi.loan.business.repository.galaxycard.impl;

import static com.tuanzi.loan.generated.jooq.loan.tables.GalaxyCardOrder.GALAXY_CARD_ORDER;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

import org.jooq.Record;
import org.jooq.RecordMapper;
import org.jooq.impl.DSL;
import org.jooq.impl.TableImpl;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import com.tuanzi.loan.business.repository.galaxycard.GalaxyCardOrderStatRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepository;
import com.tuanzi.loan.vo.request.galaxycard.GalaxyCardOrderStatRequest;
import com.tuanzi.loan.vo.response.galaxycard.GalaxyCardOrderStatResponse;
import com.tuanzi.server.loan_base.util.DateUtils;

@SuppressWarnings({ "rawtypes" })
@Repository
public class GalaxyCardOrderStatResositoryImpl extends JOOQBaseRepository implements GalaxyCardOrderStatRepository{
	
	@Override
	protected TableImpl getTable() {
		return GALAXY_CARD_ORDER;
	}

	@Override
	public Page<GalaxyCardOrderStatResponse> list(
			GalaxyCardOrderStatRequest searchRequest, Pageable pageable) {
		
		List<Object> paramList = new ArrayList<Object>();

		StringBuilder sql = new StringBuilder("SELECT a.createdate AS createDate, a.channel AS channelcode, c.`channel_name` AS channelName, IFNULL(COUNT(DISTINCT(phoneid)), 0) AS homePageUV, IFNULL(b.payOrderNum, 0) AS payOrderNum FROM `loan_galaxy_index_show_uv` AS a")
		.append(" LEFT JOIN(")
		.append(" SELECT DATE(create_time) AS cdate, COUNT(*) AS payOrderNum, channel FROM `galaxy_card_order`")
		.append(" WHERE order_status = 2 AND `channel` IN(?) GROUP BY `channel`, cdate")
		.append(" ) AS b")
		.append(" ON a.`channel` = b.channel AND a.`createdate` = b.cdate")
		.append(" LEFT JOIN comm_product_channel c ON a.`channel` = c.`channel_code`")
		.append(" WHERE a.channel IN(?)");
		
		if(searchRequest.getChannelCodeList() == null || searchRequest.getChannelCodeList().size() == 0){
			paramList.add("");
			paramList.add("");
		}else{
			paramList.add(DSL.list(searchRequest.getChannelCodeList().stream().map(DSL::val).collect(Collectors.toList())));
			paramList.add(DSL.list(searchRequest.getChannelCodeList().stream().map(DSL::val).collect(Collectors.toList())));
		}
		
		if(searchRequest.getStartDate() != null){
			sql.append(" AND a.`createdate` >= ?");
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest.getStartDate()));
		}
		
		if(searchRequest.getEndDate() != null){
			sql.append(" AND a.`createdate` <= ?");
			paramList.add(DateUtils.formatDateToYyyyMMdd(searchRequest.getEndDate()));
		}
		
		sql.append(" GROUP BY a.`createdate`,a.`channel`");
		Long count = dslContext.fetchOne("SELECT COUNT(*) FROM (" + sql + ") AS d", paramList.toArray()).into(Long.class);
		
		paramList.add(pageable.getOffset());
		paramList.add(pageable.getPageSize());
		
		return new PageImpl<GalaxyCardOrderStatResponse>(dslContext.fetch(sql.toString() + " LIMIT ?,? ", paramList.toArray()).map(new RecordMapper<Record, GalaxyCardOrderStatResponse>() {

			@Override
			public GalaxyCardOrderStatResponse map(Record record) {
				
				Date createDate = record.get("createDate", Date.class);
				String channelCode = record.get("channelcode", String.class);
				String channelName = record.get("channelName", String.class);
				Integer homePageUV = record.get("homePageUV", Integer.class);
				Integer payOrderNum = record.get("payOrderNum", Integer.class);
			    
				DecimalFormat format = new DecimalFormat("#.##%");
				String payRate = format.format((double)payOrderNum / (double)homePageUV);
				
				GalaxyCardOrderStatResponse response = new GalaxyCardOrderStatResponse();
				response.setCreateDate(createDate);
				response.setChannelCode(channelCode);
				response.setChannelName(channelName);
				response.setHomePageUV(homePageUV);
				response.setPayOrderNum(payOrderNum);
				response.setPayRate(payRate);
				return response;
			}
		}), pageable, count);
	}


}
